package com.csi.dao.impl;

import com.csi.dao.RoleDao;
import com.csi.domain.Permission;
import com.csi.domain.Role;
import com.csi.utils.DruidUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class RoleDaoImpl implements RoleDao {
    @Override
    public Map<String, Role> findAll() throws SQLException {
        Connection connection = DruidUtils.getConnection();
        PreparedStatement ps = connection.prepareStatement("SELECT * FROM role");
        Map<String, Role> roleMap = new HashMap<>();

        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            Role role = new Role();
            role.setRoleId(rs.getInt("role_id"));
            role.setRoleName(rs.getString("role_name"));
            role.setRoleDesc(rs.getString("role_desc"));

            roleMap.put(String.valueOf(role.getRoleId()), role);
        }

        String sql = """
                SELECT
                    r.role_id,
                    GROUP_CONCAT(p.permission_id SEPARATOR ',') AS permission_ids,
                    GROUP_CONCAT(p.permission_name SEPARATOR ',') AS permission_names,
                    GROUP_CONCAT(p.permission_desc SEPARATOR ',') AS permission_descs
                FROM
                    role r
                LEFT JOIN role_permission rp ON r.role_id = rp.role_id
                LEFT JOIN permission p ON rp.permission_id = p.permission_id
                GROUP BY
                    r.role_id
                ORDER BY
                    r.role_id
                """;
        PreparedStatement ps1 = connection.prepareStatement(sql);

        ResultSet resultSet = ps1.executeQuery();
        while (resultSet.next()) {
            String roleId = String.valueOf(resultSet.getInt("role_id"));
            String permissionIdsStr = resultSet.getString("permission_ids");
            String permissionNamesStr = resultSet.getString("permission_names");
            String permissionDescsStr = resultSet.getString("permission_descs");

            Role role = roleMap.get(roleId);
            if (role != null) {
                List<Permission> permissionList = new ArrayList<>();
                String[] permissionIds = permissionIdsStr == null ? new String[0] : permissionIdsStr.split(",");
                String[] permissionNames = permissionNamesStr == null ? new String[0] : permissionNamesStr.split(",");
                String[] permissionDescs = permissionDescsStr == null ? new String[0] : permissionDescsStr.split(",");
                for (int i = 0; i < permissionIds.length; i++) {
                    Permission permission = new Permission();
                    permission.setPermissionId(Integer.parseInt(permissionIds[i]));
                    permission.setPermissionName(permissionNames.length > i ? permissionNames[i] : "");
                    permission.setPermissionDesc(permissionDescs.length > i ? permissionDescs[i] : "");
                    permissionList.add(permission);
                }
                role.setPermissions(permissionList);
            }
        }

        DruidUtils.close(connection);
        return roleMap;
    }

    @Override
    public Map<String, Role> findById(int roleId) throws SQLException {
        Connection connection = DruidUtils.getConnection();
        String sql = """
                SELECT r.*,p.*
                FROM role r
                JOIN role_permission rp ON r.role_id = rp.role_id
                JOIN permission p ON rp.permission_id = p.permission_id
                WHERE r.role_id = ?;
                """;
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, roleId);

        Map<String, Role> roleMap = new HashMap<>();

        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            Role role = new Role();
            role.setRoleId(rs.getInt("role_id"));
            role.setRoleName(rs.getString("role_name"));
            role.setRoleDesc(rs.getString("role_desc"));

            roleMap.put(String.valueOf(role.getRoleId()), role);
        }

        String sql1 = """
                SELECT
                    r.role_id,
                    GROUP_CONCAT(p.permission_id SEPARATOR ',') AS permission_ids,
                    GROUP_CONCAT(p.permission_name SEPARATOR ',') AS permission_names,
                    GROUP_CONCAT(p.permission_desc SEPARATOR ',') AS permission_descs
                FROM
                    role r
                LEFT JOIN role_permission rp ON r.role_id = rp.role_id
                LEFT JOIN permission p ON rp.permission_id = p.permission_id
                WHERE r.role_id = ?
                GROUP BY
                    r.role_id
                ORDER BY
                    r.role_id
                """;
        PreparedStatement ps1 = connection.prepareStatement(sql1);
        ps1.setInt(1, roleId);

        ResultSet resultSet = ps1.executeQuery();
        while (resultSet.next()) {
            String roleIdStr = String.valueOf(resultSet.getInt("role_id"));
            String permissionIdsStr = resultSet.getString("permission_ids");
            String permissionNamesStr = resultSet.getString("permission_names");
            String permissionDescsStr = resultSet.getString("permission_descs");

            Role role = roleMap.get(roleIdStr);
            if (role != null) {
                List<Permission> permissionList = new ArrayList<>();
                String[] permissionIds = permissionIdsStr == null ? new String[0] : permissionIdsStr.split(",");
                String[] permissionNames = permissionNamesStr == null ? new String[0] : permissionNamesStr.split(",");
                String[] permissionDescs = permissionDescsStr == null ? new String[0] : permissionDescsStr.split(",");
                for (int i = 0; i < permissionIds.length; i++) {
                    Permission permission = new Permission();
                    permission.setPermissionId(Integer.parseInt(permissionIds[i]));
                    permission.setPermissionName(permissionNames.length > i ? permissionNames[i] : "");
                    permission.setPermissionDesc(permissionDescs.length > i ? permissionDescs[i] : "");
                    permissionList.add(permission);
                }
                role.setPermissions(permissionList);
            }
        }

        DruidUtils.close(connection);
        return roleMap;
    }

    @Override
    public void updateDesc(int roleId, String roleDesc) throws SQLException {
        Connection connection = DruidUtils.getConnection();

        String sql = """
                UPDATE role
                SET role_desc = ?
                WHERE role_id = ?;
                """;
        PreparedStatement ps = connection.prepareStatement(sql);

        // 设置更新语句中的参数
        ps.setString(1, roleDesc);
        ps.setInt(2, roleId);

        ps.executeUpdate();

        DruidUtils.close(connection);

    }

    @Override
    public void updatePermission(int roleId, List<Integer> permissions) throws SQLException {
        Connection connection = DruidUtils.getConnection();
        PreparedStatement ps = null;

        String sql = "DELETE FROM role_permission WHERE role_id = ?";
        ps = connection.prepareStatement(sql);
        ps.setInt(1, roleId);
        ps.executeUpdate();

        String sql1 = "INSERT INTO role_permission (role_id, permission_id) VALUES (?, ?)";
        ps = connection.prepareStatement(sql1);
        for (Integer permission : permissions) {

            ps.setInt(1, roleId);
            ps.setInt(2, permission);
            ps.executeUpdate();
        }


        DruidUtils.close(connection);
    }

    @Override
    public void add(String roleName, String roleDesc) throws SQLException {

        Connection connection = DruidUtils.getConnection();

        String sql = """
        INSERT INTO role(role_name, role_desc)
        VALUES (?, ?);
        """;
        PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

        // 设置插入语句中的参数
        ps.setString(1, roleName);
        ps.setString(2, roleDesc);

        ps.executeUpdate();

        // 获取自动生成的主键值
        ResultSet rs = ps.getGeneratedKeys();
        if (rs.next()) {
            int roleId = rs.getInt(1);

            String sql1 = """
                    INSERT INTO role_permission(role_id, permission_id)
                    VALUES (?, 4);
                    """;

            PreparedStatement ps1 = connection.prepareStatement(sql1);
            ps1.setInt(1, roleId);
            ps1.executeUpdate();
        }

        DruidUtils.close(connection);
    }
}
